﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace DLLTest.OpertionClass
{
    /*主要是数据操作类
     * 实例化数据连接，数据操作
     */
    class DataOperate
    {
        DataCon datacon = new DataCon();
        OleDbConnection oledbcon;//数据库连接
        OleDbCommand oledbcom;//数据操作命令
        OleDbDataAdapter oledbda;//用于填充DataSet
        DataSet ds;//记录集

        /// <summary>
        /// 主要为了执行ＳＱＬ语句
        /// </summary>
        /// <param name="strCon"></param>
        public void getCom(string strCon)
        {
            oledbcon = datacon.getCon();
            oledbcom = new OleDbCommand(strCon, oledbcon);
            oledbcon.Open();
            oledbcom.ExecuteNonQuery();
            oledbcon.Close();
        }
        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="strCon">数据连接</param>
        /// <param name="tbname">数据表</param>
        /// <returns></returns>
        public DataSet getDs(string strCon, string tbname)
        {
            oledbcon = datacon.getCon();
            oledbda = new OleDbDataAdapter(strCon, oledbcon);
            ds = new DataSet();
            oledbda.Fill(ds, tbname);
            return ds;
        }

        public DataSet GetDataSet()
        {
            oledbcon = datacon.getCon();
            //
            DataSet dataset = new DataSet();
            OleDbDataAdapter OleDbDA = new OleDbDataAdapter(querySqlCmd, oledbcon);
            try
            {
                oledbcon.Open();
                OleDbDA.Fill(dataset);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "获取数据失败！"); 
            }
            finally
            {
                if (oledbcon.State == ConnectionState.Open)
                {
                    oledbcon.Close();
                }
            }
            return dataset;
        }

        /// <summary>
        /// 根据SQL语句返回数据集
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sqlCmd)
        {
            oledbcon = datacon.getCon();
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter(sqlCmd, oledbcon);
            try
            {
                oledbcon.Open();
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "获取数据失败！");
            }
            finally
            {
                if (oledbcon.State == ConnectionState.Open)
                {
                    oledbcon.Close();
                }
            }
            return ds;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sqlCmd"></param>
        public void RunSqlCommand(string sqlCmd)
        {
            oledbcon = datacon.getCon();
            OleDbCommand cmd = new OleDbCommand(sqlCmd, oledbcon);
            try
            {
                oledbcon.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "操作数据库失败！");
            }
            finally
            {
                if (oledbcon.State == ConnectionState.Open)
                {
                    oledbcon.Close();
                }
            }
        }

        private string m_querySqlCmd;
        #region 查询树结构SQL语句
        public string querySqlCmd
        {
            get
            {
                if (m_querySqlCmd == null)
                {
                    return "select * from TreeNode";
                }
                return querySqlCmd;
            }
            set
            {
                m_querySqlCmd = value;
            }
        }
        #endregion

        #region 更新Status
        /// <summary>
        /// 根据ParentID更新Status状态为1
        /// </summary>
        /// <param name="parentID">父ID，一级节点ＩＤ</param>
        public void UpStatus(string parentID)
        {
            try
            {
                string updateSqlCmd = "update TreeNode set Status= " + 1 + " where ParentID = '" + parentID + "'";
                RunSqlCommand(updateSqlCmd);
            }
            catch { }
        }
         /// <summary>
        /// 关闭时将所有通道都更新为0
        /// </summary>
        public void UpStatus_1()
        {
            try
            {
                string updateSqlCmd = "update TreeNode set Status= " + 0 + " where ChannelNO <> '" + "-1" + "'";
                RunSqlCommand(updateSqlCmd);
            }
            catch { }
        }
        #endregion
        #region 更新LoginID
        /// <summary>
        /// 根据ID号更新LoginID状态为1
        /// </summary>
        /// <param name="parentID">父ID，一级节点ＩＤ</param>
        public void UPLoginID(string ploginID, Int32 parentID)
        {
            try
            {
                string updateSqlCmd = "update TreeNode set LoginID= '" + ploginID+"'" + " where ChannelNo = '" + "-1" + "'" + " and ID=" + parentID;
                RunSqlCommand(updateSqlCmd);
            }
            catch { }
        }
        #endregion
        /// <summary>
        /// SqlDataAdapter
        /// </summary>
        /// <param name="Strsql">sql语句</param>
        /// <returns>返回SqlDataAdapter</returns>
        public OleDbDataAdapter sqlsda(string strsql)
        {
            oledbcon = datacon.getCon();
            OleDbDataAdapter sda = new OleDbDataAdapter(strsql,oledbcon);
            return sda;
        }

        /// <summary>
        /// 根据ParentID查询通道最大值
        /// </summary>
        /// <param name="parentID"></param>
        /// <returns></returns>
        public string strmax(string parentID)
        {
            string maxID;
            oledbcon = datacon.getCon();
            oledbcon.Open();
            OleDbDataAdapter oledb = new OleDbDataAdapter();
            oledb.SelectCommand = new OleDbCommand("select max(ChannelNO) from TreeNode where ParentID='"+parentID+"'", oledbcon);
            //oledb.SelectCommand = new OleDbCommand("select max(" + strData + ")" + " from " + strTable, Connection);
            maxID = (string)oledb.SelectCommand.ExecuteScalar();
            oledbcon.Close();
            return maxID;
        }

        public bool DellTree(string table,Int32 id)
        { 
            bool dellOK=false;
            try
            {
                string deleteSqlCmd = "delete * from " + table + " where ID=" + id;
                RunSqlCommand(deleteSqlCmd);
                dellOK = true;
            }catch{
                dellOK = false;
            }
            return dellOK; 
        }
    }
}
